﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using BAL.Entities;
using DAL.SQLStatements;
using BAL.Utils;

namespace DAL.LogicComponents
{
    public class clsCityDAL
    {
        #region --- Get User's City and Country From Data
        public static DataRow queryGetUserCiCo(clsUser obj)
        {
            // Select all of products by user id
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblCity as ci, tblCountry as co";
            query.propColumn = "ci.CityName, ci.CityID, co.Country, co.CountryID";
            query.propWhere = "ci.CountryID = co.CountryID and ci.CityID=" + obj.CityID;

            // return result
            return query.getDatatable().Rows[0];
        }
        #endregion ---
        
        #region --- Get All City
        public static DataTable queryGetCityList()
        {
            // Select all of products by user id
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblCity, tblCountry";
            query.propColumn = "CityID,CityName,Country";
            query.propWhere = "tblCity.CountryID = tblCountry.CountryID and IsTrash = 1";

            // return result
            return query.getDatatable();
        }
        #endregion ---

        #region --- Get All City by id
        public static DataTable queryGetCityByID(clsCity obj)
        {
            // Select all of products by user id
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblCity";
            query.propColumn = "*";
            query.propWhere = "CityID = '" + obj.CityID + "' and IsTrash = 1";

            // return result
            return query.getDatatable();
        }
        #endregion ---

        #region --- Get City in a Country
        public static DataTable queryGetCity(clsCountry obj)
        {
            // Select all of products by user id
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblCity";
            query.propColumn = "CityName, CityID";
            query.propWhere = "CountryID=" + obj.CountryID;

            // return result
            return query.getDatatable();
        }
        #endregion ---

        #region --- Add new city
        public static bool queryAddCity(clsCity obj)
        {
            // Add value to column(s)
            Dictionary<String, Object> data = new Dictionary<String, Object>();
            data.Add("CountryID", obj.CountryID);
            data.Add("CityName", "N'" + obj.CityName + "'");
            data.Add("InsertDate", "'"+clsDateTimeSetting.UTCTime()+"'");
            data.Add("IsTrash", 1);

            //Execute insert query
            clsQueryBuilder query = new clsQueryBuilder();
            return query.executeInsertInto("tblCity", data);
        }
        #endregion

        #region --- Update city
        public static bool queryUpdateCity(clsCity obj)
        {
            Dictionary<String, Object> cols = new Dictionary<String, Object>();
            cols.Add("CountryID", obj.CountryID);
            cols.Add("CityName", "N'"+obj.CityName+"'");
            cols.Add("LastUpdate", "'"+clsDateTimeSetting.UTCTime()+"'");
            // Execute update query
            clsQueryBuilder query = new clsQueryBuilder();
            return query.executeUpdate("tblCity", cols, String.Format("CityID = {0}", obj.CityID));

        }
        #endregion

        #region --- check City exist
        public static bool CheckCityExist(clsCity obj)
        {
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblCity";
            query.propColumn = "CityName";
            query.propWhere = "CityID > 0 and CityName = N'" + obj.CityName + "'";

            return query.getBoolValue();
        }
        #endregion

       
    }
}